# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        黄周云  
# Name:         test14_10
# Description:  例14.10
# Author:       ASUS
# Date:         2019/4/23
#-------------------------------------------------------------------------------


from pymysql import *
import json
#打开数据库,返回一个Connection对象
def connectDB():
    db = connect("127.0.0.1","root","980721","school",charset="utf8")
    return db
db = connectDB()
def createTable(db):
    #创建游标
    cursor = db.cursor()
    sql = '''CREATE TABLE persons
    (id INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    age INT NOT NULL,
    address CHAR(50),
    salary REAL);'''
    try:
        #执行sql语句
        cursor.execute(sql)
        #提交
        db.commit()
        return True
    except:
        #如果发生错误就回滚
        db.rollback()
    return False
def insertRecords(db):
    cursor = db.cursor()
    try:
        cursor.execute("DELETE FROM persons")
        cursor.execute("INSERT INTO persons (id,name,age,address,salary) VALUES (1,'Paul',32,'California',20000.0)")
        cursor.execute("INSERT INTO persons (id,name,age,address,salary) VALUES (2,'Bill',25,'California',20000.0)")
        cursor.execute("INSERT INTO persons (id,name,age,address,salary) VALUES (3,'Cindy',23,'California',20000.0)")
        cursor.execute("INSERT INTO persons (id,name,age,address,salary) VALUES (4,'Mike',25,'California',20000.0)")
        db.commit()
        return True
    except Exception as e:
        print(e)
        db.rollback()
    return False
def selectRecords(db):
    cursor = db.cursor()
    sql = 'SELECT name,age,salary FROM persons ORDER BY age DESC'
    cursor.execute(sql)
    #调用fetchall方法获取全部记录
    results = cursor.fetchall()
    print(results)
    #将查询结果组织成其他形式
    field = ['name','age','salary']
    records =[]
    for row in results:
        records.append(dict(zip(field,row)))
    return json.dumps(records)

if createTable(db):
    print("成功创建persons表")
else:
    print('persons表已存在')

if insertRecords(db):
    print('成功插入记录')
else:
    print('插入记录失败')
print(selectRecords(db))
#关闭数据库
db.close()








